Declare @ProjectGUID as uniqueidentifier
Set @ProjectGUID = '13B098BF-2BA2-4456-A6FF-B33F81802B86'

select System_Id as ReqID, System_Title as ReqTitle, 
	(
		select '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', ' 
		from CurrentWorkItemView CWV2
		join vFactLinkedCurrentWorkItem FLCW2 on FLCW2.TargetWorkitemSK = CWV2.WorkItemSK
		where SourceWorkItemSK = FLCW.SourceWorkItemSK 
		and System_WorkItemType = 'Task'
		for XML PATH('') 
	 ) as TaskID,
	 (
		select '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', ' 
		from CurrentWorkItemView CWV2
		join vFactLinkedCurrentWorkItem FLCW2 on FLCW2.TargetWorkitemSK = CWV2.WorkItemSK
		where SourceWorkItemSK = FLCW.SourceWorkItemSK 
		and System_WorkItemType = 'Change Request'
		for XML PATH('') 
	 ) as ChangeRequestID,
	 (
		select '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', ' 
		from CurrentWorkItemView CWV2
		join vFactLinkedCurrentWorkItem FLCW2 on FLCW2.TargetWorkitemSK = CWV2.WorkItemSK
		where SourceWorkItemSK = FLCW.SourceWorkItemSK 
		and System_WorkItemType = 'Test Case'
		for XML PATH('') 
	 ) as TestID,
	 (
	   select distinct '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', '
	   from vFactLinkedCurrentWorkItem FLCW2 
	   join vFactLinkedCurrentWorkItem FLCW3 on FLCW3.SourceWorkitemSK = FLCW2.TargetWorkitemSK
	   join CurrentWorkItemView CWV2 on CWV2.WorkItemSK = FLCW3.TargetWorkItemSK
	   where FLCW2.SourceWorkItemSK = FLCW.SourceWorkItemSK 
	   and System_WorkItemType = 'Bug'
	   for XML PATH('') 
	 ) as BugID,
	 (
		select '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', ' 
		from CurrentWorkItemView CWV2
		join vFactLinkedCurrentWorkItem FLCW2 on FLCW2.TargetWorkitemSK = CWV2.WorkItemSK
		where SourceWorkItemSK = FLCW.SourceWorkItemSK 
		and System_WorkItemType = 'Bug'
		for XML PATH('') 
	 ) as BugID2
from CurrentWorkItemView CWV
left join vFactLinkedCurrentWorkItem FLCW on FLCW.SourceWorkItemSK = CWV.WorkItemSK
where 
CWV.ProjectNodeGUID = @ProjectGUID and 
System_WorkItemType = 'Requirement'
Group by FLCW.SourceWorkItemSK, System_Id, System_Title
order by System_Id asc

--TODO
--Generize for all work items that might be added in the future to the Req. Category